﻿Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Imports System.IO
Imports extranet.BE
Imports MySql.Data.MySqlClient

Public Class DACurso
  Private objBD As New BancoDeDados
  Public objQuery As New BEQuery

  Sub New(ByVal booCondicaoSimilar As Boolean)
    objQuery.Where.CondicaoSimilar = booCondicaoSimilar
  End Sub

  Public Sub New()
  End Sub

  Public Sub Cadastra(ByVal objBECurso As BECurso)
    Try
      objBD.duplicaAspasSimples(objBECurso, GetType(BECurso))
      objBD.ExecutaNonQuery("INSERT INTO ext_cursos (nome, ementa, ativo, datacadastro) " & _
                            "VALUES ('" & objBECurso.Titulo & "', '" & _
                                          objBECurso.Ementa & "', '" & _
                                          objBECurso.Ativo & "', '" & _
                                          Now().ToString("yyyy/MM/dd HH:mm") & "');" & _
                            "SELECT LAST_INSERT_ID();")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DACurso.Cadastra(): " & ex.Message)
    End Try
  End Sub

  Public Sub Atualiza(ByVal objBECurso As BECurso)
    Try
      objBD.duplicaAspasSimples(objBECurso, GetType(BECurso))
      objBD.ExecutaNonQuery("UPDATE ext_cursos " & _
                            "SET nome='" & objBECurso.Titulo & "', " & _
                                "ementa='" & objBECurso.Ementa & "', " & _
                                "ativo='" & objBECurso.Ativo & "' " & _
                            "WHERE id=" & objBECurso.Id & ";")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DACurso.Atualiza(): " & ex.Message)
    End Try
  End Sub

  Public Sub RelacionaCursoProva(ByVal objBECurso As BECurso)
    Try
      objBD.duplicaAspasSimples(objBECurso, GetType(BECurso))
      objBD.ExecutaNonQuery("UPDATE ext_cursos " & _
                            "SET provaId=" & objBECurso.Prova.Id & " " & _
                            "WHERE id=" & objBECurso.Id & ";")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DACurso.RelacionaCursoProva(): " & ex.Message)
    End Try
  End Sub

  Public Sub Exclui(ByVal objBECurso As BECurso)
    Try
      ' Excluir do Banco de Dados
      objBD.ExecutaNonQuery("DELETE FROM ext_cursos WHERE id=" & objBECurso.Id & ";")
      ' Ler Diretório atual
      Dim strDiretorio As String = objBECurso.Diretorio
      If Not strDiretorio.EndsWith("\") Then strDiretorio &= "\"
      ' Apagar Material do Curso
      My.Computer.FileSystem.DeleteDirectory(strDiretorio, FileIO.DeleteDirectoryOption.DeleteAllContents)
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DACurso.Exclui(): " & ex.Message)
    End Try
  End Sub

  Public Function SelecionaCurso(ByVal objBECurso As BECurso) As BECurso
    Try
      objQuery.Where.CondicaoSimilar = False
      Dim objBECursos As List(Of BECurso) = Seleciona(objBECurso)
      If objBECursos.Count > 0 Then
        objBECurso = objBECursos(0)
      Else
        objBECurso = Nothing
      End If
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DACurso.Seleciona(): " & ex.Message)
    End Try
    Return objBECurso
  End Function

  Public Function Seleciona(ByVal objBECurso As BECurso) As List(Of BECurso)
    Dim objBECursos As List(Of BECurso) = New List(Of BECurso)
    Try
      objBD.duplicaAspasSimples(objBECurso, GetType(BECurso))
      objQuery._Select = "SELECT c.*, (SELECT count(*) from ext_rel_alunos_cursos WHERE idCurso=c.id) qtdeAlunos FROM ext_cursos c"
      If objBECurso IsNot Nothing Then
        If objBECurso.Id > 0 Then objQuery.Where.AdicionarCondicaoWhere("c.id", objBECurso.Id, False)
        objQuery.Where.AdicionarCondicaoWhere("c.ementa", objBECurso.Ementa)
        objQuery.Where.AdicionarCondicaoWhere("c.nome", objBECurso.Titulo)
        If objBECurso.Ativo >= 0 Then objQuery.Where.AdicionarCondicaoWhere("c.ativo", objBECurso.Ativo, False)
        If objBECurso.Alunos IsNot Nothing AndAlso objBECurso.Alunos.Count() > 0 Then
          objQuery.Where.AdicionarCondicaoWhereFixo("c.id IN (SELECT idCurso " & _
                                                                "FROM ext_rel_alunos_cursos " & _
                                                                "WHERE usuario='" & objBECurso.Alunos(0).Usuario & "')")
        End If
      End If
      objBECursos = SelecionaCursos()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DACurso.Seleciona(): " & ex.Message)
    End Try
    Return objBECursos
  End Function

  Public Function SelecionaCursosDisponiveis(ByVal objBECurso As BECurso) As List(Of BECurso)
    Dim objBECursos As List(Of BECurso) = New List(Of BECurso)
    Try
      objBD.duplicaAspasSimples(objBECurso, GetType(BECurso))
      objQuery._Select = "SELECT c.*, (SELECT count(*) from ext_rel_alunos_cursos WHERE idCurso=c.id) qtdeAlunos FROM ext_cursos c"
      If objBECurso IsNot Nothing Then
        If objBECurso.Id > 0 Then objQuery.Where.AdicionarCondicaoWhere("c.id", objBECurso.Id, False)
        objQuery.Where.AdicionarCondicaoWhere("c.ementa", objBECurso.Ementa)
        objQuery.Where.AdicionarCondicaoWhere("c.nome", objBECurso.Titulo)
        If objBECurso.Ativo >= 0 Then objQuery.Where.AdicionarCondicaoWhere("c.ativo", objBECurso.Ativo, False)
        If objBECurso.Alunos IsNot Nothing AndAlso objBECurso.Alunos.Count() > 0 Then
          objQuery.Where.AdicionarCondicaoWhereFixo("NOT c.id IN (SELECT idCurso " & _
                                                                "FROM ext_rel_alunos_cursos " & _
                                                                "WHERE usuario='" & objBECurso.Alunos(0).Usuario & "')")
        End If
      End If
      objBECursos = SelecionaCursos()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DACurso.Seleciona(): " & ex.Message)
    End Try
    Return objBECursos
  End Function

  Private Function SelecionaCursos() As List(Of BECurso)
    Dim objBECursos As List(Of BECurso) = New List(Of BECurso)
    Dim objBECurso As BECurso
    Dim strProvaId As String
    Try
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            objBECurso = New BECurso()
            With objBECurso
              .Id = objSQLDReader("id")
              .Titulo = objSQLDReader("nome")
              .Ementa = objSQLDReader("ementa")
              .Ativo = IIf(objSQLDReader("ativo"), 1, 0)
              .QtdeAlunos = objSQLDReader("qtdeAlunos")

              strProvaId = objSQLDReader("provaid").ToString()
              .Prova = New BEProva(IIf(strProvaId.Length > 0, strProvaId, 0))
              objBECurso.DataCadastro = objSQLDReader("datacadastro")
            End With
            objBECursos.Add(objBECurso)
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DACurso.SelecionaCursos(): " & ex.Message)
    End Try
    Return objBECursos
  End Function

  Public Sub AdicionarAlunos(ByVal objBECurso As BECurso)
    Dim strSQL As String = ""
    Try
      For Each objBEAluno As BEAluno In objBECurso.Alunos
        strSQL &= "INSERT INTO ext_rel_alunos_cursos (idCurso, usuario, dataInscricao) " & _
                  "VALUES (" & objBECurso.Id & ", '" & _
                               objBEAluno.Usuario & "', '" & _
                               Now().ToString("yyyy/MM/dd HH:mm") & "');"
      Next
      Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
      objComando.ExecuteNonQuery()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DACurso.AdicionarAlunos(): " & ex.Message)
    End Try

  End Sub

  Public Sub RemoverAlunos(ByVal objBECurso As BECurso)
    Dim strSQL As String = ""
    Try
      For Each objBEAluno As BEAluno In objBECurso.Alunos
        strSQL &= "DELETE FROM ext_rel_alunos_cursos WHERE idCurso=" & objBECurso.Id & _
                                                     " AND usuario='" & objBEAluno.Usuario & "';"
      Next
      Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
      objComando.ExecuteNonQuery()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DACurso.RemoverAluno(): " & ex.Message)
    End Try
  End Sub

End Class
